package com.zx.deploy.common.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.zx.deploy.model.DatabaseConfig;

import sun.security.jca.GetInstance;

public class DBUtil2 {
	private String databaseType = "MySql";
	private String driver = "com.mysql.jdbc.Driver";
	private String databaseName;
	private String url;
	private String userName;
	private String password;
	
	private static DBUtil2 instance;

	// 定义数据库的链接
	private Connection connection;

	// 定义sql语句的执行对象
	private PreparedStatement pstmt;

	private Statement stmt;

	// 定义查询返回的结果集合
	private ResultSet resultSet;

	private DBUtil2(DatabaseConfig config) {
		this.url = config.datasource_url;
		this.userName = config.userName;
		this.password = config.password;
		this.databaseType = config.databaseType;
		this.driver = config.driver;
		this.databaseName = config.databaseName;
	}

	public static DBUtil2 getInstance(DatabaseConfig config) {
		   if(instance==null){
	            instance= new DBUtil2(config);
	        }
		return instance;
	}

	public final synchronized Connection getConnection() {
		try {
			if (connection != null && !connection.isClosed()) {
	            return connection;
			}
			Class.forName(driver);
			connection = DriverManager.getConnection(url, userName, password);
		} catch (Exception e) {
			throw new RuntimeException("get connection error!", e);
		}
		return connection;
	}

	/**
	 * 释放资源
	 */
	public void releaseConn() {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public int setupdateData(String sql) throws Exception {
		Connection conn = getConnection();
		try {
			stmt = conn.createStatement();
			return stmt.executeUpdate(sql);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}
	}

	public int updateExecuteBatch(List<String> sqlList) throws Exception {
		Connection conn = getConnection();
		conn.setAutoCommit(false);
		try {
			stmt = conn.createStatement();
			for (String sql : sqlList) {
				stmt.addBatch(sql);
			}
			int[] updateCounts = stmt.executeBatch();
			conn.commit();
			return updateCounts.length;
		} catch (Exception e) {
			conn.rollback();
			throw new Exception(e.getMessage());
		}
	}

	public List<Map<String, Object>> queryForList(String sql) throws Exception {
		ResultSetMetaData rsmd = null;
		int maxSize = -1;
		String[] fields = (String[]) null;
		List times = new ArrayList();
		List rows = new ArrayList();
		Map row = null;

		Connection connection = getConnection();
		pstmt = connection.prepareStatement(sql);
		resultSet = pstmt.executeQuery();
		rsmd = resultSet.getMetaData();

		maxSize = rsmd.getColumnCount();
		fields = new String[maxSize];
		for (int i = 0; i < maxSize; ++i) {
			fields[i] = rsmd.getColumnLabel(i + 1);
			if ("java.sql.Timestamp".equals(rsmd.getColumnClassName(i + 1))) {
				times.add(fields[i]);
			}
		}

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		while (resultSet.next()) {
			row = new LinkedHashMap();
			for (int i = 0; i < maxSize; ++i) {
				Object value = (times.contains(fields[i])) ? resultSet.getTimestamp(fields[i])
						: resultSet.getObject(fields[i]);
				if ((times.contains(fields[i])) && (value != null)) {
					value = sdf.format(value);
				}
				row.put(fields[i], value);
			}
			rows.add(row);
		}
		return rows;
	}

	public List<Map<String, Object>> queryForList2(String sql) throws Exception {
		ResultSetMetaData rsmd = null;
		int maxSize = -1;
		String[] fields = (String[]) null;

		List rows = new ArrayList();
		Map row = null;

		Connection connection = getConnection();
		pstmt = connection.prepareStatement(sql);
		resultSet = pstmt.executeQuery();
		rsmd = resultSet.getMetaData();

		maxSize = rsmd.getColumnCount();
		fields = new String[maxSize];

		while (resultSet.next()) {
			row = new HashMap();
			for (int i = 0; i < maxSize; ++i) {
				row.put(rsmd.getColumnLabel(i + 1), resultSet.getObject(rsmd.getColumnLabel(i + 1)));
			}
			rows.add(row);
		}

		return rows;
	}

	public List<Map<String, Object>> queryForListPageForMSSQL(String sql, int maxRow, int beginIndex) throws Exception {
		ResultSetMetaData rsmd = null;
		int maxSize = -1;
		String[] fields = (String[]) null;
		List times = new ArrayList();
		List rows = new ArrayList();
		Map row = null;

		Connection connection = getConnection();

		pstmt = connection.prepareStatement(sql, 1005, 1008);

		pstmt.setMaxRows(maxRow);

		resultSet = pstmt.executeQuery();
		rsmd = resultSet.getMetaData();

		maxSize = rsmd.getColumnCount();
		fields = new String[maxSize];
		for (int i = 0; i < maxSize; ++i) {
			fields[i] = rsmd.getColumnLabel(i + 1);
			if ("java.sql.Timestamp".equals(rsmd.getColumnClassName(i + 1))) {
				times.add(fields[i]);
			}
		}

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		resultSet.absolute(beginIndex);

		while (resultSet.next()) {
			row = new HashMap();
			for (int i = 0; i < maxSize; ++i) {
				Object value = (times.contains(fields[i])) ? resultSet.getTimestamp(fields[i])
						: resultSet.getObject(fields[i]);
				if ((times.contains(fields[i])) && (value != null)) {
					value = sdf.format(value);
				}
				row.put(fields[i], value);
			}
			rows.add(row);
		}
		return rows;
	}

	public List<Map<String, Object>> queryForListWithType(String sql) {
		List rows2 = new ArrayList();
		try {
			Connection connection = getConnection();
			pstmt = connection.prepareStatement(sql);
			resultSet = pstmt.executeQuery();
			ResultSetMetaData rsme = resultSet.getMetaData();
			int columnCount = rsme.getColumnCount();

			resultSet.next();
			for (int i = 1; i < columnCount + 1; ++i) {
				Map map = new HashMap();

				map.put("column_name", rsme.getColumnName(i));

				map.put("column_value", resultSet.getObject(rsme.getColumnName(i)));

				map.put("data_type", rsme.getColumnTypeName(i));

				map.put("precision", Integer.valueOf(rsme.getPrecision(i)));

				map.put("isAutoIncrement", Boolean.valueOf(rsme.isAutoIncrement(i)));

				map.put("is_nullable", Integer.valueOf(rsme.isNullable(i)));

				map.put("isReadOnly", Boolean.valueOf(rsme.isReadOnly(i)));

				rows2.add(map);
			}
		} catch (Exception e) {
			System.out.println("queryForListWithType  " + e.getMessage());
		}
		return rows2;
	}

	public List<Map<String, Object>> queryForColumnOnly(String sql) {

		List rows2 = new ArrayList();
		try {
			Connection connection = getConnection();
			pstmt = connection.prepareStatement(sql);
			resultSet = pstmt.executeQuery();
			ResultSetMetaData rsme = resultSet.getMetaData();
			int columnCount = rsme.getColumnCount();

			for (int i = 1; i < columnCount + 1; ++i) {
				Map map = new HashMap();

				map.put("column_name", rsme.getColumnName(i));

				map.put("data_type", rsme.getColumnTypeName(i));

				map.put("precision", Integer.valueOf(rsme.getPrecision(i)));

				map.put("isAutoIncrement", Boolean.valueOf(rsme.isAutoIncrement(i)));

				map.put("is_nullable", Integer.valueOf(rsme.isNullable(i)));

				map.put("isReadOnly", Boolean.valueOf(rsme.isReadOnly(i)));

				rows2.add(map);
			}
		} catch (Exception e) {
			System.out.println("queryForColumnOnly  " + e.getMessage());
		}
		return rows2;
	}

	public List<Map<String, Object>> executeSqlForColumns(String sql) throws Exception {
		ResultSetMetaData rsmd = null;
		int maxSize = -1;
		List rows = new ArrayList();

		Connection connection = getConnection();
		pstmt = connection.prepareStatement(sql);
		resultSet = pstmt.executeQuery();
		rsmd = resultSet.getMetaData();
		maxSize = rsmd.getColumnCount();

		for (int i = 0; i < maxSize; ++i) {
			Map map = new HashMap();

			map.put("column_name", rsmd.getColumnLabel(i + 1));

			map.put("data_type", rsmd.getColumnTypeName(i + 1));

			rows.add(map);
		}
		return rows;
	}

	public int executeQueryForCount(String sql) {
		int rowCount = 0;
		try {
			Connection connection = getConnection();
			stmt = connection.createStatement();
			resultSet = stmt.executeQuery(sql);
			while (resultSet.next()) {
				Object count = resultSet.getObject("count(*)");
				rowCount = Integer.parseInt(count.toString());
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return rowCount;
	}

	public int executeQueryForCountForPostgesSQL(String sql) {
		int rowCount = 0;
		try {
			Connection connection = getConnection();
			stmt = connection.createStatement();
			resultSet = stmt.executeQuery(sql);
			while (resultSet.next()) {
				Object count = resultSet.getObject("count");
				rowCount = Integer.parseInt(count.toString());
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return rowCount;
	}

	public int executeQueryForCount2(String sql) {
		int rowCount = 0;
		Connection connection = getConnection();
		try {
			stmt = connection.createStatement();
			resultSet = stmt.executeQuery(sql);
			resultSet.last();
			rowCount = resultSet.getRow();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return rowCount;
	}

	public boolean executeQuery(String sql) {
		boolean bl = false;
		Connection connection = getConnection();
		try {
			stmt = connection.createStatement();
			resultSet = stmt.executeQuery(sql);
			if (!(resultSet.next()))
				return true;
		} catch (Exception localException) {
		}
		return bl;
	}

	public boolean testConn() {
		boolean bl = false;
		Connection conn = getConnection();
		if (conn != null)
			bl = true;
		try {
			conn.close();
		} catch (Exception localException) {
		}
		return bl;
	}

	public String getPrimaryKeys(String databaseName, String tableName) {
		List rows2 = new ArrayList();
		try {
			Connection connection = getConnection();
			DatabaseMetaData metadata = connection.getMetaData();
			resultSet = metadata.getPrimaryKeys(databaseName, null, tableName);
			if (!(resultSet.next())) {
				System.out.println("主键名称: " + resultSet.getString(4));
				return resultSet.getString(4);
			}
		} catch (Exception e) {
			System.out.println("queryForColumnOnly  " + e.getMessage());
		}
		return "";
	}

	public List<String> getPrimaryKeyss(String databaseName, String tableName) {
		Connection conn = null;
		List rows2 = new ArrayList();
		try {
			conn = getConnection();
			DatabaseMetaData metadata = conn.getMetaData();

			resultSet = metadata.getPrimaryKeys(databaseName, null, tableName);
			while (resultSet.next()) {
				System.out.println("主键名称2: " + resultSet.getString(4));
				rows2.add(resultSet.getString(4));
			}
		} catch (Exception e) {
			System.out.println("queryForColumnOnly  " + e.getMessage());
		}
		return rows2;
	}

	public int executeQueryForCountForOracle(String sql) {
		int rowCount = 0;
		Connection conn = getConnection();
		String sql3 = " select count(*) as count from  (" + sql + ")";
		try {
			stmt = conn.createStatement();
			resultSet = stmt.executeQuery(sql3);
			resultSet.next();
			rowCount = resultSet.getInt("count");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return rowCount;
	}

	public int executeQueryForCountForPostgreSQL(String sql) {
		int rowCount = 0;
		Connection conn = getConnection();
		String sql3 = " select count(*) as count from  (" + sql + ") t ";
		try {
			stmt = conn.createStatement();
			resultSet = stmt.executeQuery(sql3);
			resultSet.next();

			rowCount = resultSet.getInt("count");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return rowCount;
	}
}
